#!/bin/bash
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi

dwd_traffic_page_view_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_page_view_inc partition (dt= '$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    source_id,
    source_site,
    source_url,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,
    concat(mid_id,'-',last_value(session_start_point,true) over (partition by mid_id order by ts)) session_id,
    during_time
from
(
    select
        common.ar province_id,
        common.ba brand,
        common.ch channel,
        common.is_new is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        common.sc source_id,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        displays.display_type,
        ts,
        if(page.last_page_id is null,ts,null) session_start_point
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and page is not null
)log
left join (
        select
        id,
        source_site,
        source_url
        from ${APP}.ods_base_source_full
        where dt='$do_date'
) source
on log.source_id=source.id;
"
dwd_course_audition_inc="
insert overwrite table ${APP}.dwd_course_audition_inc partition(dt='$do_date')
select
       log.uid user_id,
       log.video_id video_id,
       log.create_time create_time,
       video.course_id course_id,
       course.subject_id subject_id,
       subject.category_id category_id
from(
    select
    common.uid uid,
    appvideo.video_id video_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') create_time
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and appvideo.video_id is not null

)log
left join
(
    select
        data.id id,
        data.course_id course_id
    from ${APP}.ods_video_info_inc
    where dt='$do_date'
)video
on log.video_id=video.id
left join(
    select
        id,
        subject_id
    from ${APP}.ods_course_info_full
    where dt='$do_date'
) course
on course.id=video.course_id
left join (
    select
        id,
        category_id
    from ${APP}.ods_base_subject_info_full
    where dt='$do_date'
) subject
on subject.id=course.subject_id;
"
dwd_user_register_inc="
insert overwrite table ${APP}.dwd_user_register_inc partition(dt)
select
    ui.user_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system,
    date_format(create_time,'yyyy-MM-dd')
from
(
    select
        data.id user_id,
        data.create_time
    from ${APP}.ods_user_info_inc
    where dt='$do_date'
    and type='bootstrap-insert'
)ui
left join
(
    select
        common.ar province_id,
        common.ba brand,
        common.ch channel,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and \`start\`.first_open = '1'
    and common.uid is not null
)log
on ui.user_id=log.user_id;
"


dwd_user_login_inc="
insert overwrite table ${APP}.dwd_user_login_inc partition (dt = '$do_date')
select user_id,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd')          date_id,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') login_time,
       channel,
       province_id,
       version_code,
       mid_id,
       brand,
       model,
       operate_system
from (
         select user_id,
                channel,
                province_id,
                version_code,
                mid_id,
                brand,
                model,
                operate_system,
                ts
         from (
                  select user_id,
                         channel,
                         province_id,
                         version_code,
                         mid_id,
                         brand,
                         model,
                         operate_system,
                         ts,
                         row_number() over (partition by session_id order by ts) rn
                  from (
                           select user_id,
                                  channel,
                                  province_id,
                                  version_code,
                                  mid_id,
                                  brand,
                                  model,
                                  operate_system,
                                  ts,
                                  concat(mid_id, '-', last_value(session_start_point, true)
                                  over (partition by mid_id order by ts)) session_id
                           from (
                                    select common.uid                              user_id,
                                           common.ch                               channel,
                                           common.ar                               province_id,
                                           common.vc                               version_code,
                                           common.mid                              mid_id,
                                           common.ba                               brand,
                                           common.md                               model,
                                           common.os                               operate_system,
                                           ts,
                                           if(page.last_page_id is null, ts, null) session_start_point
                                    from ${APP}.ods_log_inc
                                    where dt = '$do_date'
                                      and page is not null
                                ) t1
                       ) t2
                  where user_id is not null
              ) t3
         where rn = 1
     ) t4;
     "
dwd_action_chapter_process_inc="insert overwrite table ${APP}.dwd_action_chapter_process_inc partition(dt)
select
b.video_id,
a.chapter_id,
a.course_id,
a.user_id,
a.position_sec,
b.during_sec,
date_format(create_time, 'yyyy-MM-dd')
from
(select

               data.chapter_id,
               data.course_id,
               data.user_id,
               data.position_sec,
               data.create_time
from ${APP}.ods_user_chapter_process_inc
WHERE dt='$do_date'
and type='insert') a join
(select
        data.chapter_id,
       data.id video_id,
       data.during_sec
from ${APP}.ods_video_info_inc
where dt = '$do_date'
and type='bootstrap-insert') b

on a.chapter_id = b.chapter_id;
"
dwd_action_test_inc="
insert overwrite table ${APP}.dwd_action_test_inc partition(dt)
select
a.id exam_id,
a.paper_id,
b.course_id,
a.user_id,
a.score,
a.duration_sec,
date_format(create_time, 'yyyy-MM-dd')
from
(select

               data.id,
               data.paper_id,
               data.user_id,
               data.score,
               data.duration_sec,
               data.create_time
from ${APP}.ods_test_exam_inc
WHERE dt='$do_date'
and type='insert') a join

(select
        data.id,
       data.course_id
from ${APP}.ods_test_paper_inc
where dt = '$do_date') b

on a.paper_id = b.id;
"
dwd_action_question_inc="
insert overwrite table ${APP}.dwd_action_question_inc partition(dt)
select
a.exam_id,
a.paper_id,
a.question_id,
a.user_id,
a.is_correct,
date_format(create_time, 'yyyy-MM-dd')
from
(select

               data.exam_id,
               data.paper_id,
               data.question_id,
               data.user_id,
               data.is_correct,
               data.create_time
from ${APP}.ods_test_exam_question_inc
WHERE dt='$do_date'
and type='insert') a;
"
dwd_trade_order_info_inc="
insert overwrite table ${APP}.dwd_trade_order_info_inc partition(dt)
select
    c.course_id,
    c.order_id,
    c.user_id,
    c.final_amount,
    c.province_id,
    d.total_amount,
    d.payment_status,
    date_format(create_time, 'yyyy-MM-dd')
from
(
    select
        a.course_id,
        a.order_id,
        a.user_id,
        b.final_amount,
        b.province_id,
        a.create_time
    from
        (
            select
               data.course_id,
               data.order_id,
               data.user_id,
               data.create_time
            from ${APP}.ods_order_detail_inc
            WHERE dt='$do_date'
            and type='bootstrap-insert'
        ) a
    join
        (
        select
           data.id,
           data.final_amount,
           data.province_id
        from ${APP}.ods_order_info_inc
        where dt = '$do_date'
        and type='bootstrap-insert'
        ) b
    on a.order_id = b.id
) c
join
(
    select
        data.order_id,
        data.total_amount,
        data.payment_status
    from ${APP}.ods_payment_info_inc
    where dt = '$do_date'
    and type='bootstrap-insert'
) d
on c.order_id = d.order_id;
"
dwd_course_review_info_inc="
insert overwrite table ${APP}.dwd_course_review_info_inc partition(dt)
select
       data.id id,
       data.user_id user_id,
       data.course_id course_id,
       data.review_txt review_txt,
       data.review_stars review_stars,
       date_format(data.create_time, 'yyyy-MM-dd')
from ${APP}.ods_review_info_inc
where dt = '$do_date'
and type='insert';
"
dwd_trade_cart_add_inc="
insert overwrite table ${APP}.dwd_trade_cart_add_inc partition (dt)
select id,
       user_id,
       course_id,
       date_format(create_time, 'yyyy-MM-dd') date_id,
       date_format(create_time, 'yyyy-MM-dd')
from (
         select data.id,
                data.user_id,
                data.course_id,
                data.create_time
         from ${APP}.ods_cart_info_inc
         where dt = '$do_date'
           and type = 'insert'
     ) ci;"

case $1 in
    "dwd_traffic_page_view_inc" )
        hive -e "$dwd_traffic_page_view_inc"
    ;;
    "dwd_course_audition_inc" )
        hive -e "$dwd_course_audition_inc"
    ;; 
    "dwd_user_register_inc" )
        hive -e "$dwd_user_register_inc"
    ;; 
    "dwd_user_login_inc" )
        hive -e "$dwd_user_login_inc"
    ;; 
     "dwd_action_chapter_process_inc" )
        hive -e "$dwd_action_chapter_process_inc"
    ;; 
    "dwd_action_test_inc" )
        hive -e "$dwd_action_test_inc"
    ;; 
    "dwd_action_question_inc" )
        hive -e "$dwd_action_question_inc"
    ;; 
     "dwd_trade_order_info_inc" )
        hive -e "$dwd_trade_order_info_inc"
    ;; 
     "dwd_course_review_info_inc" )
        hive -e "$dwd_course_review_info_inc"
    ;; 
    "dwd_trade_cart_add_inc" )
        hive -e "$dwd_trade_cart_add_inc"
    ;; 
    "all" )
        hive -e "$dwd_traffic_page_view_inc$dwd_user_register_inc$dwd_user_login_inc$dwd_user_dwd_course_audition_inc_inc$dwd_action_chapter_process_inc$dwd_action_test_inc$dwd_action_question_inc$dwd_trade_order_info_inc$dwd_course_review_info_inc$dwd_trade_cart_add_inc"
    ;;
esac
